/****** Object:  Stored Procedure dbo.asi_CreateUpdateAppealParticipation    Script Date: 7/3/2003 4:58:02 PM ******/
-- Create or update AppealParticipation records for each contact in the input list
--
CREATE PROCEDURE [dbo].[asi_CreateUpdateAppealParticipation]
@listKey uniqueidentifier,
@appealKey uniqueidentifier,
@solicitationKey uniqueidentifier,
@sourceCodeKey uniqueidentifier,
@responseTypeCode int,
@userKey uniqueidentifier
AS
BEGIN

BEGIN TRAN

declare @returnCount int
set @returnCount = 0

-- Update AppealParticipation for source code + contacts combos
-- that are already in appeal
UPDATE vBoAppealParticipation
SET    SolicitationKey=@solicitationKey,
    UpdatedByUserKey=@userKey,
    UpdatedOn=getdate()
FROM    vBoAppealParticipation ap INNER JOIN vBoListElement le
    ON ap.RespondentUserKey = le.ObjectKey
WHERE
    ap.AppealKey = @appealKey
    AND le.ListKey = @listKey
    AND ap.SourceCodeKey=@sourceCodeKey

set @returnCount = @returnCount + @@ROWCOUNT

-- Insert into AppealParticipation for contacts entering appeal
-- for a particular source code
INSERT vBoAppealParticipation(
    AppealParticipationKey,
    AppealKey,
    SolicitationKey,
    SourceCodeKey,
    ResponseTypeCode,
    CreatedByUserKey,
    CreatedOn,
    UpdatedByUserKey,
    UpdatedOn,
    RespondentUserKey)
SELECT newid(),
    @appealKey,
    @solicitationKey,
    @sourceCodeKey,
    @responseTypeCode,
    @userKey,
    getdate(),
    @userKey,
    getdate(),
    le.ObjectKey
FROM     vBoListElement le
WHERE
    le.ListKey = @listKey
    AND NOT EXISTS (SELECT 1
             FROM vBoAppealParticipation ap
             WHERE ap.AppealKey = @appealKey
             AND ap.RespondentUserKey = le.ObjectKey
             AND ap.SourceCodeKey=@sourceCodeKey)

set @returnCount = @returnCount + @@ROWCOUNT

select @returnCount as ReturnCount

COMMIT TRAN

END

GO